package org.yes.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;
import java.util.List;
import java.util.ArrayList;

/**
 * User: mtymes
 * Date: Jan 21, 2010
 * Time: 3:10:43 PM
 */
public class PoiUtil {

    public static HSSFWorkbook readWorkbook(final String filename) {
        HSSFWorkbook workbook = null;

        InputStream xlsFile = null;
        try {
            File file = new File(filename);

            if (file.exists()) {
                xlsFile = new FileInputStream(filename);

                workbook = new HSSFWorkbook(xlsFile);
            }
        } catch (FileNotFoundException e) {
            throw new IllegalStateException(e);
        } catch (IOException e) {
            // todo: use commons util instead
            IOUtil.closeQuietly(xlsFile);
            throw new IllegalStateException(e);
        }

        return workbook;
    }


    public static void storeWorkbook(final String filename, final Workbook workbook) {

        FileOutputStream xlsFile = null;
        try {
            xlsFile = new FileOutputStream(filename);

            workbook.write(xlsFile);

        } catch (FileNotFoundException e) {
            throw new IllegalStateException(e);
        } catch (IOException e) {
            throw new IllegalStateException(e);
        } finally {
            // todo: use commons util instead
            IOUtil.closeQuietly(xlsFile);
        }
    }


    public static List<String> getAllSheetNames(final Workbook workbook) {
        final List<String> sheetNames = new ArrayList<String>();

        int sheetCount = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            sheetNames.add(workbook.getSheetAt(i).getSheetName());
        }

        return sheetNames;
    }


    public static String getStringCellValue(final Cell cell) {
        Cell valueCell = getValueCellFromMergedRegion(cell);

        String value = null;
        if (valueCell != null) {
            switch (valueCell.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = Boolean.toString(valueCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double doubleValue = valueCell.getNumericCellValue();
                    long longValue = (long) doubleValue;

                    if (doubleValue == ((double) longValue)) {
                        value = Long.toString(longValue);
                    } else {
                        value = Double.toString(doubleValue);
                    }
                    break;
                case Cell.CELL_TYPE_FORMULA:
                case Cell.CELL_TYPE_STRING:
                default:
                    value = valueCell.getStringCellValue();
            }
        }

        return value;
    }


    public static Boolean getBooleanCellValue(final Cell cell) {
        Cell valueCell = getValueCellFromMergedRegion(cell);

        Boolean value = null;
        if (valueCell != null) {
            switch (valueCell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    value = valueCell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    value = Boolean.valueOf(valueCell.getStringCellValue());
                    break;
                default:
                    value = valueCell.getBooleanCellValue();
                    break;
            }
        }

        return value;
    }


    public static Integer getIntegerCellValue(final Cell cell) {
        Cell valueCell = getValueCellFromMergedRegion(cell);

        Integer value = null;
        if (valueCell != null) {
            switch (valueCell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    value = (int) valueCell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = (int) valueCell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    String stringValue = valueCell.getStringCellValue();
                    if (stringValue != null && !"".equals(stringValue.trim())) {
                        value = Integer.valueOf(stringValue);
                    }
                    break;
                default:
                    value = (int) valueCell.getNumericCellValue();
                    break;
            }
        }

        return value;
    }


    public static Cell getValueCellFromMergedRegion(Cell cell) {
        Cell valueCell = cell;

        if (cell != null) {
            Sheet sheet = cell.getSheet();
            int regionCount = sheet.getNumMergedRegions();
            for (int regionIndex = 0; regionIndex < regionCount; regionIndex++) {
                CellRangeAddress cellRangeAddress = sheet.getMergedRegion(regionIndex);
                if (cellRangeAddress.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
                    valueCell = sheet.getRow(cellRangeAddress.getFirstRow()).getCell(cellRangeAddress.getFirstColumn());
                    break;
                }
            }
        }

        return valueCell;
    }


    public static Cell getOrCreateCell(Sheet sheet, int rowIndex, int colIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }

        Cell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }

        return cell;
    }
}